<?php 
    // mysql的索引与慢查询
    
    // 1.通过自定义函数构建海量表
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i =i+1;
end while;
return return_str;
end$$

create function rand_num()
returns int(5)
begin
declare i int default 0;
set i =floor(10+rand()*500);
return i;
end $$

create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1;
insert into emp values ((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end$$

// 开启一个信任权限,否则调用自定义函数插入数据的时候会报错终止运行
set global log_bin_trust_function_creators=1;

// 严格大小写的查询
select*from emp where binary ename like 'XQkErW';
// 如果想在建表的时候就强制大小写查询
create talbe table_name (value varchar(20) binary);

// mysql的数据库和表存放的默认目录是mysql根目录下的data文件夹下,*.frm是表结构文件内含索引,*.idb是数据文件

// 查看索引的几种方法
    show keys from emp\G
    show index from emp\G
    desc emp;
// 2.添加索引(索引要看表数据所占大小而定,因为生成索引的过程中会产生巨大的文件,若表数据过大,则需要先考虑分表)
    // 1)主键索引.可以在创建表的时候指定,也可后期修改,如下
            alter table emp add primary key (empno);
            主键索引的特点:
            1)一个表最多只能有一个主键
            2)一个主键可以指向多个列
            3)主键索引的效率是最高的,一般给自增id
            4)主键索引的列的值必须唯一,也不能为空

        // 2)唯一索引.可以在创建表的时候添加unique(顾名思义,如果当前列中有重复的值,是不可以添加unique的,会报duplicate),也可后期修改,如下两种方法:
                1)create unique index uni_ename on emp (ename);
                2)删除索引:drop index uni_ename on emp
                   添加索引:alter table emp add unique (ename);
             唯一索引的特点:
            1)一张表可以有多个唯一索引
            2)唯一索引不能重复,但如果没有指定not null的话,是可以为空的(不是空字符串,是null)
            3)只有在没有重复数据的时候,才能考虑使用唯一索引
            4)唯一索引的效率仅次于主键索引
        // 3)普通索引.方法如下:
                1)create index ind_ename on emp (ename);
                2)drop index ind_ename on emp;
                   alter table emp add index (ename);
            普通索引的特点:
            1)一张表中可以有多个普通索引,一个普通索引也可以指向多列
            2)普通索引列中的数据是可以重复的
            3)普通索引的效率相对较低
        // 4)全文索引.使用方法:
            mysql默认的全文索引,需要用match(字段名1,字段名2,...) against('关键字')
            查询语句:select*from articles where match(title,body) against('word');

            全文索引的特点:
            1)mysql默认的全文索引,表引擎必须为myisam
            2)mysql默认的全文索引,只支持英文
            3)停止词:对于特别普通的字词,不会建索引,例如'a',用全文索引查不出任何结果
            4)匹配度:全文索引是按照一定概率匹配的

        // 如何解决mysql全文索引不支持中文?
        MysqlCft
        
//下面是创建全文索引表的方式        
create table articles(
    id int unsigned auto_increment not null primary key,
    title varchar(200),
    body text,
    fulltext(title,body)
)engine=myisam charset=utf8; 
// 3.使用慢查询定位.默认情况下mysql是不会记录慢查询的.需要手动开启.
    我的版本是mysql5.6,在my.ini中加上下面三句话
    ong_query_time=2  
    slow_query_log=1  
    slow_query_log_file=c:/wamp2.5/bin/mysql/mysql5.6.17/data/localhost-xiyou-slowsql.log

    重启mysql

    在启动完成后也可以通过修改变量的方式设置慢查询时间:
    set long_query_time=1;

    然后通过select variables like '%slow%'查看slow_query_log是否为ON,如果不是,使用set global slow_query_log='ON'
    来开启

    当查询时间大于设定时间的,都会被记录的log文件中
    Time                 Id Command    Argument
    # Time: 160317 14:05:14
    # User@Host: root[root] @ localhost [::1]  Id:     1
    # 重点是下面这行:Query_time是查询时间,如果过慢要考虑索引;Lock_time是锁定时间也就是查询前的等待时间,如果过慢要考虑读写分离
    # Query_time: 5.667325  Lock_time: 0.001000 Rows_sent: 0  Rows_examined: 8000000
    use mysqltest;
    SET timestamp=1458194714;
    select * from emp where binary ename like 'mKUKUI';

// 4.索引的正确使用
    1)对于创建的多列(复合)索引,只要查询条件使用了最左边的列,索引一般就会被使用
    2)对于使用like 的查询,如果是'%word'或者'_word'的形式,则不会使用索引.'word%'是会使用索引的.
    3)查询条件中有'or',则所有条件必须都有索引才能使用索引查询,否则无效.
    4)如果查询字段是字符串类型,即便存在数据全是数字的形式,也必须用把查询的值放到引号中,否则不会使用索引
    5)某些时候mysql自身判断使用全表扫描比索引快,也不会使用索引
    
    // 查询索引的使用效率
    mysql> show status like 'handler_read%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | Handler_read_first    | 0     | #索引中第一条被读的次数.如果较高,它建议服务器正执行大量全索引扫描
    | Handler_read_key      | 0     | #【重点】这个值较高,说明查询和表的索引正确.
    | Handler_read_last     | 0     | #
    | Handler_read_next     | 0     | #按照键顺序读下一行的请求数.如果你用范围约束或执行索引扫描来查询索引列,该值会增加
    | Handler_read_prev     | 0     | #按照键顺序读前一行的请求数.该方法主要用于优化order by.....desc
    | Handler_read_rnd      | 0     | #根据固定位置读一行的请求数.如果你正执行大量查询并需要对结果进行排序该值较高,你可能
                                           #使用了大量需要mysql扫描整个表的查询,或者你的连接没有正确使用键 
    | Handler_read_rnd_next | 0     |#【重点】根据数据文件中读下一行的请求数.如果你正进行大量的表扫描,该值较高,通常说明你的表索引
                                          #不正确,或者写入的查询没有使用索引
    +-----------------------+-------+

// 5.优化SQL语句
    1)默认情况下,mysql会对所有的group by进行某种排序,这样会在大量数据的情况下造成性能大幅降低,所以这种时候要配合
    order by null来关闭默认排序.例如:
        select*from emp group by ename order by null;
    2)某些情况下,连接查询比子查询效率更高.因为join模式下mysql不需要在内存中创建临时表.
    3)一定要在多条件下使用'or'的话,尽量给每个查询字段都加上索引
    4）（作了解）大批量插入数据：
        1)对于MyIsam
            alter table talbe_name disable keys;//禁用所有的键,不执行会一条条检测,很慢
            insert ........;//执行写入
            alter table table_name enable keys;//开启键
        2)对于InnoDb
            先将要导入的数据按照主键排序
            set unique_checks=0 //关闭唯一性校验
            set autocommit=0 //关闭自动提交

    行级锁:InnoDb支持.并发性更好,安全性高,但速度慢,批量插入速度低.InnoDb是发展的大趋势.最大支持64TB的存储限制.而且独有支持外键.
            对于安全性要求高的表,需要用到事务的支持,所以需要用InnoDb.
    表级锁:MyIsam支持.查询速度更快,批量插入速度高,但并发性差.无存储限制.鉴于特性,更多适合于只有读和插入操作(不包括更新和删除)频繁的表.
    Memory/Heap:这两种类型的表用于访问/变化频繁且不需要长期保存的数据,因为每当mysql关闭的时候,数据会全部丢失(表结构还在),例如用户在线状态.现在多被Redis/Memcache取代

    insert into users select * from users;//自我复制

    // 以MyIsam引擎表为代表,长期使用会留下一堆碎片文件,所以需要定期碎片整理,否则会大量占用磁盘空间.
    optimize table emp;

    // mysql中的date格式默认最大支持到2038年,所以要考虑配合php使用int系列类型存储更晚的时间
 ?>